﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TableToEntity
{
    /// <summary>
    /// 生成MsSql数据库实体逻辑
    /// </summary>
    public static class CreateMsSqlModel
    {
        #region 获取基本信息

        private static readonly string infoSql = @"select * from (select 
                                                        [tbname]=c.Name,
	                                                    (case when a.Name='gCode' then 'true' else 'false' end) isPrimarykey,
                                                        [tabDescribe]=isnull(f.[value],''),
                                                        [colName]=a.Name,
                                                        [dbType]=b.Name,
                                                        [byteLength]=case when a.[max_length]=-1 and b.Name!='xml' then 2147483647
                                                                when b.Name='xml' then 2147483647
                                                                else rtrim(a.[max_length]) end,
                                                        [dbLength]=ColumnProperty(a.object_id,a.Name,'Precision'),
                                                        [decimalDigits]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
                                                        [required]=case when a.is_nullable=1 then 'false' else 'true' end,
                                                        [colDescribe]=isnull(e.[value],''),
                                                        [devalue]=isnull(d.text,'')    
                                                    from 
                                                        sys.columns a 
                                                    left join
                                                        sys.types b on a.user_type_id=b.user_type_id
                                                    inner join
                                                        sys.objects c on a.object_id=c.object_id and c.Type='U'
                                                    left join
                                                        syscomments d on a.default_object_id=d.ID
                                                    left join
                                                        sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 
                                                    left join
                                                        sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1) info
                                                    where info.[tbname] = '{0}'";

        private static readonly string VIEWS_LIST_SQL = @"select v.name from sys.views as v,sys.schemas as s where v.schema_id = s.schema_id and s.[name] = 'dbo'";


        private static readonly string VIEWS_INFO_SQL = @"select distinct * from (select 
                                                                [tbname]=c.Name,    
                                                                [colName]=a.Name,
                                                                [dbType]=b.Name    
                                                            from 
                                                                sys.columns a 
                                                            left join
                                                                sys.types b on a.user_type_id=b.user_type_id
                                                            inner join
                                                                sys.objects c on a.object_id=c.object_id and c.Type='V'
                                                            left join
                                                                syscomments d on a.default_object_id=d.ID
                                                            left join
                                                                sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 
                                                            left join
                                                                sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1) info
                                                            where info.[tbname] = '{0}'";



        #endregion

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="address">链接地址</param>
        /// <param name="dbName">数据库名称</param>
        /// <param name="account">账号</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        private static string GetConnectionStr(DBInfo dbInfo)
        {
            string conStr = $"server={dbInfo.Service},{dbInfo.Port};database={dbInfo.DbName};User = {dbInfo.Account}; Password ={dbInfo.Password};pooling=true;Min pool size = 1;Max pool size = 10;Connection lifetime = 0;Connection timeout = 5;";

            return conStr;
        }

        /// <summary>
        /// 获取数据库中的表
        /// </summary>
        /// <param name="type">0表 1视图</param>
        /// <returns></returns>
        public static  string[] GetTableNames(int type, DBInfo dbInfo, ModelInfo modelInfo)
        {
            string[] resultList = null;
            SqlConnection conn = null;
            string conStr = GetConnectionStr(dbInfo);
            try
            {
                conn = new SqlConnection(conStr);
                conn.Open();

                if (type == 0)
                {
                    string[] restrictions = new string[4];
                    List<string> tabNames = new List<string>();
                    restrictions[0] = modelInfo.ModelNameSpace;
                    DataTable table = conn.GetSchema("Tables");

                    conn.Close();

                    foreach (System.Data.DataRow row in table.Rows)
                    {
                        tabNames.Add(row.ItemArray[2].ToString());
                    }

                    resultList= tabNames.ToArray<string>();
                }
                else
                {
                    List<string> tabNames = new List<string>();
                    using (SqlCommand command = new SqlCommand(VIEWS_LIST_SQL, conn))
                    {
                        var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                        {
                            tabNames.Add(reader["name"].ToString());
                        }
                        reader.Close();
                    }
                    resultList= tabNames.ToArray();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return resultList;
        }

        /// <summary>
        /// 获取数据库中的表信息
        /// </summary>
        /// <param name="tbnames"></param>
        /// <param name="dbInfo"></param>
        /// <returns></returns>
        public static Dictionary<string, List<FiledInfo>> GetTableInfo(string[] tbnames, DBInfo dbInfo)
        {
            var ret = new Dictionary<string, List<FiledInfo>>();
            SqlConnection conn = null;
            string conStr = GetConnectionStr(dbInfo);

            try
            {
                conn = new SqlConnection(conStr);
                conn.Open();

                foreach (var item in tbnames)
                {
                    string sql = string.Format(infoSql, item);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    List<FiledInfo> filed = new List<FiledInfo>();
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            FiledInfo fi = new FiledInfo();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                fi.isPrimarykey = reader["isPrimarykey"].ToString();
                                fi.colName = reader["colName"].ToString();
                                fi.dbType = reader["dbType"].ToString();
                                fi.colDescribe = reader["colDescribe"].ToString();
                                fi.dbLength = reader["dbLength"].ToString();
                                fi.decimalDigits = reader["decimalDigits"].ToString();
                                fi.devalue = reader["devalue"].ToString();
                                fi.required = reader["required"].ToString();
                                fi.byteLength = reader["byteLength"].ToString();
                                fi.tabDescribe = reader["tabDescribe"].ToString();
                            }
                            filed.Add(fi);
                        }
                    }
                    ret.Add(item, filed);
                }
              
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return ret;

        }


        /// <summary>
        /// 获取数据库中的视图信息
        /// </summary>
        /// <param name="viewNames"></param>
        /// <param name="dbInfo"></param>
        /// <returns></returns>
        public static Dictionary<string, List<FiledInfo>> GetViewInfo(string[] viewNames, DBInfo dbInfo)
        {
            Dictionary<string, List<FiledInfo>> ret = new Dictionary<string, List<FiledInfo>>();
            SqlConnection conn = null;
            string conStr = GetConnectionStr(dbInfo);

            try
            {
                conn = new SqlConnection(conStr);

                foreach (var item in viewNames)
                {
                    conn.Open();
                    string sql = string.Format(VIEWS_INFO_SQL, item);
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        List<FiledInfo> filed = new List<FiledInfo>();
                        using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                FiledInfo fi = new FiledInfo();
                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    fi.colName = reader["colName"].ToString();
                                    fi.dbType = reader["dbType"].ToString();
                                }
                                filed.Add(fi);
                            }
                        }
                        ret.Add(item, filed);
                    }
                }
                return ret;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}
